<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of InvoiceController
 *
 * @author Poh Nean
 */
class ExportController extends PsController
{

	public $defaultAction = 'invoices';
	public $layout = 'manage';

	public function accessRules()
	{
		return CMap::mergeArray(array(
					array(
						'allow',
						'roles' => array(
							'Invoice.Manage',
							'Receipt.Manage',
							'PaymentVoucher.Manage'
						),
					)), parent::accessRules()
		);
	}

	public function moduleAccessRules()
	{
		return array(
			array('Propsage.Accounting.Export', array('exportCsv', 'exportQuickbooks')),
			array('Propsage.Accounting.Commission'),
			array('Propsage.Accounting')
		);
	}

	public function actionInvoices($from_date = null, $to_date = null, $action = null, $show_deleted = 0)
	{
		if ($action == 'Clear Filters') {
			$this->redirect(array($this->route));
		}

		$data = array();
		$data['from_date'] = $from_date;
		$data['to_date'] = $to_date;
		$data['show_deleted'] = $show_deleted;
		$data['type'] = 'Invoices';

		if (isset($from_date)) {
			$to_date = empty($to_date) ? date("Y-m-d") : $to_date;

			$invoice = new Invoice('search');
			$gridColumns = array(
				'invoice_date' => array(
					'header' => $invoice->getAttributeLabel('invoice_date'),
					'type' => 'date',
					'name' => 'invoice_date'
				),
				'invoice_number' => array(
					'header' => $invoice->getAttributeLabel('invoice_number'),
					'name' => 'invoice_number'
				),
				'bill_to_party' => array(
					'header' => $invoice->getAttributeLabel('bill_to_name'),
					'name' => 'bill_to_party'
				),
				'due_date' => array(
					'header' => $invoice->getAttributeLabel('due_date'),
					'type' => 'date',
					'name' => 'due_date'
				),
				'terms' => array(
					'header' => $invoice->getAttributeLabel('terms'),
					'name' => 'terms'
				),
				'description' => array(
					'header' => $invoice->getAttributeLabel('description'),
					'name' => 'description'
				),
				'amount' => array(
					'header' => $invoice->getAttributeLabel('amount'),
					'name' => 'amount',
					'value' => 'Yii::app()->numberFormatter->formatCurrency($data["amount"], $data["currency_code"])'
				),
			);

			$exportColumns = array();
			$exportColumns[] = 'i.invoice_date';
			$exportColumns[] = 'i.invoice_number';
			$exportColumns[] = 'i.to_party_id as bill_to_party_code';
			$exportColumns[] = 'i.bill_to_name AS bill_to_party';
			$exportColumns[] = 'i.bill_to_address';
			$exportColumns[] = 'i.currency_code';
			$exportColumns[] = 'i.due_date';
			$exportColumns[] = 'i.description';
			$exportColumns[] = 'i.message';
			$exportColumns[] = 'i.terms';
			$exportColumns[] = 'i.deleted';
			$exportColumns[] = 'i.delete_reason';
			$exportColumns[] = 'i.sent';
			$exportColumns[] = 'ii.description AD invoice_item_description';
			$exportColumns[] = 'ii.quantity';
			$exportColumns[] = 'ii.unit_price';
			$exportColumns[] = 'ii.gst_amount';
			$exportColumns[] = 'ii.amount';

			$command = Yii::app()->db->createCommand();
			$command->select($exportColumns);
			$command->from(array('invoice i'));
			$command->join('invoice_item ii', 'i.id = ii.invoice_id');
			$command->where('i.owner_party_id = :owner_party_id', array(':owner_party_id' => Yii::app()->user->model->agency->party_id));
			$command->andWhere('i.invoice_date BETWEEN :from AND :to', array(':from' => $from_date, ':to' => $to_date));
			if ($show_deleted === 0) {
				$command->andWhere('i.deleted = 0');
			}
			$command->order = 'i.invoice_date ASC';

			$countCommand = Yii::app()->db->createCommand();
			$countCommand->select(array('COUNT(*)'));
			$countCommand->from('(' . $command->getText() . ') AS t1');
			$countCommand->params = $command->params;
			$totalCount = $countCommand->queryScalar();

			$pagination = new CPagination;
			$pagination->pageSize = isset($_GET['pagesize']) ? $_GET['pagesize'] : 20;

			$dataProvider = new CSqlDataProvider($command, array('totalItemCount' => $totalCount, 'pagination' => $pagination));

			$data['columns'] = $gridColumns;
			$data['dataProvider'] = $dataProvider;

			if ($action == 'Export') {
				Yii::import('common.lib.ECSVExport');
				$csv = new ECSVExport($command, FALSE);
				$content = $csv->toCSV();
				Yii::app()->getRequest()->sendFile($data['type'] . '_' . $from_date . '_to_' . $to_date . '.csv', $content, "text/csv", true);

				Yii::app()->end();
			}
		}

		$this->render('export', $data);
	}

	public function actionReceipts($from_date = null, $to_date = null, $action = null, $show_deleted = 0)
	{
		if ($action == 'Clear Filters') {
			$this->redirect(array($this->route));
		}

		$data = array();
		$data['from_date'] = $from_date;
		$data['to_date'] = $to_date;
		$data['show_deleted'] = $show_deleted;
		$data['type'] = 'Receipts';

		if (isset($from_date)) {
			$to_date = empty($to_date) ? date("Y-m-d") : $to_date;

			$receipt = new Receipt('search');

			$gridColumns = array(
				'payment_date' => array(
					'header' => $receipt->getAttributeLabel('payment_date'),
					'type' => 'date',
					'name' => 'payment_date'
				),
				'payment_number' => array(
					'header' => $receipt->getAttributeLabel('payment_number'),
					'name' => 'payment_number'
				),
				'payer_name' => array(
					'header' => $receipt->getAttributeLabel('payer_name'),
					'name' => 'Debtor_Name'
				),
				'comment' => array(
					'header' => $receipt->getAttributeLabel('comment'),
					'name' => 'comment'
				),
				'amount' => array(
					'header' => $receipt->getAttributeLabel('amount'),
					'name' => 'amount',
					'value' => 'Yii::app()->numberFormatter->formatCurrency($data["amount"], $data["currency_code"])'
				),
			);

			$exportColumns[] = 'p.payment_date';
			$exportColumns[] = 'p.payment_number';
			$exportColumns[] = 'p.payment_method_type_code AS Payment_Mode';
			$exportColumns[] = 'p.payment_ref_no AS Payment_Mode_Reference';
			$exportColumns[] = 'p.currency_code';
			$exportColumns[] = 'p.amount';
			$exportColumns[] = 'p.deleted';
			$exportColumns[] = 'p.delete_reason';
			$exportColumns[] = 'p.comment';
			$exportColumns[] = 'p.payer_name as Debtor_Name';
			$exportColumns[] = 'p.payer_party_id as Debtor_Code';
			$exportColumns[] = 'i.invoice_number';

			$command = Yii::app()->db->createCommand();
			$command->select($exportColumns);
			$command->from(array('payment p'));
			$command->join('payment_application pa', 'pa.payment_id = p.id');
			$command->join('invoice i', 'i.id = pa.invoice_id ');
			$command->where('p.owner_party_id = :owner_party_id', array(':owner_party_id' => Yii::app()->user->model->agency->party_id));
			$command->andWhere('p.payment_type_code = "Receipt"');
			$command->andWhere('p.payment_date BETWEEN :from AND :to', array(':from' => $from_date, ':to' => $to_date));
			$command->andWhere('i.deleted = 0');
			if ($show_deleted === 0) {
				$command->andWhere('p.deleted = 0');
			}
			$command->order = 'p.payment_date ASC';

			$countCommand = Yii::app()->db->createCommand();
			$countCommand->select(array('COUNT(*)'));
			$countCommand->from('(' . $command->getText() . ') AS t1');
			$countCommand->params = $command->params;
			$totalCount = $countCommand->queryScalar();

			$pagination = new CPagination;
			$pagination->pageSize = isset($_GET['pagesize']) ? $_GET['pagesize'] : 20;

			$dataProvider = new CSqlDataProvider($command, array('totalItemCount' => $totalCount, 'pagination' => $pagination));

			$data['columns'] = $gridColumns;
			$data['dataProvider'] = $dataProvider;

			if ($action == 'Export') {
				Yii::import('common.lib.ECSVExport');
				$csv = new ECSVExport($command, FALSE);
				$content = $csv->toCSV();
				Yii::app()->getRequest()->sendFile($data['type'] . '_' . $from_date . '_to_' . $to_date . '.csv', $content, "text/csv", true);

				Yii::app()->end();
			}
		}

		$this->render('export', $data);
	}

	public function actionPaymentVouchers($from_date = null, $to_date = null, $action = null, $show_deleted = 0)
	{
		if ($action == 'Clear Filters') {
			$this->redirect(array($this->route));
		}

		$data = array();
		$data['from_date'] = $from_date;
		$data['to_date'] = $to_date;
		$data['show_deleted'] = $show_deleted;
		$data['type'] = 'Payment Vouchers';

		if (isset($from_date)) {
			$to_date = empty($to_date) ? date("Y-m-d") : $to_date;

			$paymentVoucher = new PaymentVoucher('search');

			$gridColumns = array(
				'payment_date' => array(
					'header' => $paymentVoucher->getAttributeLabel('payment_date'),
					'type' => 'date',
					'name' => 'payment_date'
				),
				'payment_number' => array(
					'header' => $paymentVoucher->getAttributeLabel('payment_number'),
					'name' => 'payment_number'
				),
				'payer_name' => array(
					'header' => $paymentVoucher->getAttributeLabel('payee_name'),
					'name' => 'Creditor_Name'
				),
				'comment' => array(
					'header' => $paymentVoucher->getAttributeLabel('comment'),
					'name' => 'comment'
				),
				'amount' => array(
					'header' => $paymentVoucher->getAttributeLabel('amount'),
					'name' => 'amount',
					'value' => 'Yii::app()->numberFormatter->formatCurrency($data["amount"], $data["currency_code"])'
				),
			);


			$exportColumns[] = 'p.payment_date';
			$exportColumns[] = 'p.payment_number';
			$exportColumns[] = 'p.payment_method_type_code';
			$exportColumns[] = 'p.payment_ref_no';
			$exportColumns[] = 'p.currency_code';
			$exportColumns[] = 'p.amount';
			$exportColumns[] = 'p.deleted';
			$exportColumns[] = 'p.delete_reason';
			$exportColumns[] = 'p.comment';
			$exportColumns[] = 'p.payee_name as Creditor_Name';
			$exportColumns[] = 'p.payee_party_id as Creditor_Code';
			$exportColumns[] = 'p.issued_date AS Collection_Date';
			$exportColumns[] = 'pi.net_amount';
			$exportColumns[] = 'pi.comm_type';
			$exportColumns[] = 'pi.description';
			$exportColumns[] = 'r.payment_number AS Related_Receipt';

			//NEW
			$command = Yii::app()->db->createCommand();
			$command->select($exportColumns);
			$command->from(array('payment p'));
			$command->join('payable_item pi', 'p.id = pi.payment_voucher_id');
			$command->join('payment r', 'r.id=pi.payment_id');
			$command->where('p.owner_party_id = :owner_party_id', array(':owner_party_id' => Yii::app()->user->model->agency->party_id));
			$command->andWhere('p.payment_type_code = "Payment Voucher"');
			$command->andWhere('p.payment_date BETWEEN :from AND :to', array(':from' => $from_date, ':to' => $to_date));
			if ($show_deleted === 0) {
				$command->andWhere('p.deleted = 0');
			}
			$command->group(array('p.id'));
			$command->order = 'p.payment_date ASC';

			$countCommand = Yii::app()->db->createCommand();
			$countCommand->select(array('COUNT(*)'));
			$countCommand->from('(' . $command->getText() . ') AS t1');
			$countCommand->params = $command->params;
			$totalCount = $countCommand->queryScalar();

			$pagination = new CPagination;
			$pagination->pageSize = isset($_GET['pagesize']) ? $_GET['pagesize'] : 20;

			$dataProvider = new CSqlDataProvider($command, array('totalItemCount' => $totalCount, 'pagination' => $pagination));

			$data['columns'] = $gridColumns;
			$data['dataProvider'] = $dataProvider;

			if ($action == 'Export') {
				Yii::import('common.lib.ECSVExport');
				$csv = new ECSVExport($command, FALSE);
				$content = $csv->toCSV();
				Yii::app()->getRequest()->sendFile($data['type'] . '_' . $from_date . '_to_' . $to_date . '.csv', $content, "text/csv", true);

				Yii::app()->end();
			}
		}

		$this->render('export', $data);
	}

	public function actionTransactions($from_date = null, $to_date = null, $action = null, $show_deleted = 0)
	{
		if ($action == 'Clear Filters') {
			$this->redirect(array($this->route));
		}

		$data = array();
		$data['from_date'] = isset($from_date) ? $from_date : date("Y") . '-01-01';
		$data['to_date'] = isset($to_date) ? $to_date : date('Y-m-d');
		$tmp_toDate = isset($to_date) ? date('Y-m-d', strtotime($to_date . ' + 1 day')) : date('Y-m-d');
		$data['show_deleted'] = $show_deleted;
		$data['type'] = 'Transactions';

		if (isset($from_date)) {
			$to_date = empty($to_date) ? date("Y-m-d") : $to_date;

			$gridColumns = array(
				array(
					'name' => 'submit_date',
					'header' => 'Submit Date',
					'htmlOptions' => array('width' => '40px'),
				),
				array(
					'name' => 'transaction_type',
					'header' => 'Type',
					'htmlOptions' => array('width' => '120px'),
				),
				array(
					'name' => 'prop_type',
					'header' => 'Property Type',
					'htmlOptions' => array('width' => '120px'),
				),
				array(
					'name' => 'address',
					'header' => 'Address',
				),
				array(
					'name' => 'transaction_price',
					'header' => 'Transaction Price',
				),
				array(
					'name' => 'associate_agent',
					'header' => 'Associate Agents'
				),
				array(
					'name' => 'gross_fees',
					'header' => 'Gross Fees',
					'htmlOptions' => array('style' => 'text-align:left'),
				),
			);

//			dump(Yii)

			$columns = array();
//			$columns[] = "t.id";
			$columns[] = "t.case_number as tx_id";
			$columns[] = "DATE(CONVERT_TZ(t.submit_time, 'UTC', :user_timezone)) AS submit_date";
			$columns[] = "GROUP_CONCAT(pca.name SEPARATOR  ' & ') as associate_agent";
			$columns[] = "t.type AS transaction_type";
			$columns[] = "p.prop_type";
			$columns[] = "p.prop_address_formatted AS address";
			$columns[] = "t.price AS transaction_price";
			$columns[] = "t.lease_duration AS lease_duration";
			$columns[] = "(CASE WHEN t.type = 'Lease by Landlord' OR t.type='Lease by Tenant' THEN t.lease_start_date ELSE t.option_grant_date END) AS transaction_date";
			$columns[] = "(CASE WHEN t.comm_gst_payable = 1 AND t.comm_gst_inclusive=1  THEN ROUND(t.comm_dollar_value / (1+0.07), 2) ELSE t.comm_dollar_value END) AS tx_gross_comm";
			$columns[] = "(CASE WHEN t.comm_gst_payable = 1 AND t.comm_gst_inclusive=1  THEN ROUND(0.07 * t.comm_dollar_value / (1+0.07), 2) WHEN t.comm_gst_payable = 1 AND t.comm_gst_inclusive=0 THEN ROUND(0.07 * t.comm_dollar_value, 2) ELSE 0 END) AS tx_gst";
			$columns[] = "(CASE WHEN t.comm_gst_payable = 1 AND t.comm_gst_inclusive=0  THEN ROUND((1+0.07) * t.comm_dollar_value, 2) ELSE t.comm_dollar_value END) AS tx_comm";
			$columns[] = "(SELECT COALESCE(SUM(pca2.gross_comm_amount), 0) FROM `property_case_agent` pca2 WHERE pca2.property_case_id=t.id AND pca2.deleted=0 AND pca2.is_external=1 GROUP BY pca2.is_external) as external_cobroke";
			$columns[] = "t.approval_status";
//			$columns[] = "(SELECT COUNT(DISTINCT ii.invoice_id) FROM `invoice_item` ii INNER JOIN `invoice` i ON (ii.invoice_id = i.id AND i.deleted = 0 AND i.invoice_type_code = 'Invoice') WHERE ii.deleted = 0 AND ii.property_case_id = t.id) AS invoice_count";
//			$columns[] = "(SELECT COALESCE(SUM(ii.amount), 0) FROM `invoice_item` ii INNER JOIN `invoice` i ON (ii.invoice_id = i.id AND i.deleted = 0 AND i.invoice_type_code = 'Invoice') WHERE ii.deleted = 0 AND ii.property_case_id = t.id) AS invoice_total";
//			$columns[] = "(SELECT COUNT(DISTINCT pa.payment_id) FROM `payment_application` pa INNER JOIN `payment` p ON (pa.payment_id = p.id AND p.deleted = 0) WHERE pa.invoice_id IN (SELECT ii.invoice_id FROM `invoice_item` ii INNER JOIN `invoice` i ON (ii.invoice_id = i.id AND i.deleted = 0) WHERE ii.deleted = 0 AND ii.property_case_id = t.id)) AS receipt_count";
//			$columns[] = "(SELECT COALESCE(SUM(pa.amount), 0) FROM `payment_application` pa INNER JOIN `payment` p ON (pa.payment_id = p.id AND p.deleted = 0 AND p.payment_type_code = 'Receipt') WHERE pa.invoice_id IN (SELECT ii.invoice_id FROM `invoice_item` ii INNER JOIN `invoice` i ON (ii.invoice_id = i.id AND i.deleted = 0) WHERE ii.deleted = 0 AND ii.property_case_id = t.id)) AS total_payment_received";
			$columns[] = "SUM(pca.earn_comm_amount) AS agent_gross_comm";
			$columns[] = "pis.net_amount as agent_comm";
			$columns[] = "pit.net_amount as tier_amount";
			
//			$columns[] = "(SELECT COUNT(DISTINCT pi.payment_voucher_id) FROM `payable_item` pi INNER JOIN `payment` p ON (pi.payment_voucher_id = p.id AND p.deleted = 0 AND p.payment_type_code = 'Payment Voucher') WHERE pi.deleted = 0 AND pi.property_case_id = t.id) AS pv_count";
//			$columns[] = "(SELECT COUNT(DISTINCT pi.payment_voucher_id) FROM `payable_item` pi INNER JOIN `payment` p ON (pi.payment_voucher_id = p.id AND p.deleted = 0 AND p.payment_type_code = 'Payment Voucher' AND p.issued_date IS NOT NULL) WHERE pi.deleted = 0 AND pi.property_case_id = t.id) AS pv_collected";
//			$columns[] = "(SELECT COUNT(DISTINCT pi.payment_voucher_id) FROM `payable_item` pi INNER JOIN `payment` p ON (pi.payment_voucher_id = p.id AND p.deleted = 0 AND p.payment_type_code = 'Payment Voucher' AND p.issued_date IS NULL) WHERE pi.deleted = 0 AND pi.property_case_id = t.id) AS pv_uncollected";
//			$columns[] = "(SELECT COALESCE(SUM(pi.net_amount),0) FROM `payable_item` pi INNER JOIN `payment` p ON (pi.payment_voucher_id = p.id AND p.deleted = 0 AND p.payment_type_code = 'Payment Voucher') WHERE pi.deleted = 0 AND pi.property_case_id = t.id) AS pv_total";


			$baseCommand = Yii::app()->db->createCommand();
			$baseCommand->select($columns)
			->from('property_case t')
			->join('property p', 'p.id = t.property_id')
			->join('property_case_agent pca', 'pca.property_case_id = t.id AND pca.deleted = 0 AND pca.is_external = 0')
			->leftJoin("(SELECT t.property_case_id, t.comm_type, (sum(t.net_amount) + COALESCE(sum(pi.net_amount), 0)) as net_amount
						FROM `payable_item` t 
						LEFT JOIN `payable_item` pi ON (pi.parent_id = t.id AND pi.deleted=0)
						WHERE t.property_case_id IS NOT NULL AND t.comm_type='sales' AND t.deleted=0
						Group By t.property_case_id) pis", 'pis.property_case_id = t.id')
			->leftJoin("(SELECT t.property_case_id, t.comm_type, (sum(t.net_amount) + COALESCE(sum(pi.net_amount), 0)) as net_amount
						FROM `payable_item` t 
						LEFT JOIN `payable_item` pi ON (pi.parent_id = t.id AND pi.deleted=0)
						WHERE t.property_case_id IS NOT NULL AND t.comm_type='tier' AND t.deleted=0
						Group By t.property_case_id) pit", 'pit.property_case_id=t.id')
			->where('t.agency_id = :agencyId')
					->andWhere('t.deleted = 0')
					->andWhere('t.status = "Finalized"')
					->andWhere('t.submit_time BETWEEN :fromUTC AND  :toUTC')
					->group(array('pca.property_case_id'))
					->order(array('t.submit_time ASC'));

			$baseCommand->params = array(
				':agencyId' => Yii::app()->user->model->agency_id,
				':user_timezone' => Yii::app()->user->timezone,
				':fromUTC' => $from_date,
				':toUTC' => $tmp_toDate,
			);

			$countCommand = Yii::app()->db->createCommand();
			$countCommand->select(array('COUNT(*)'));
			$countCommand->from('(' . $baseCommand->getText() . ') AS t1');
			$countCommand->params = $baseCommand->params;
			$totalCount = $countCommand->queryScalar();

			$command = Yii::app()->db->createCommand();
			$command->select(array(
				't1.*',
//				'(t1.tx_comm - t1.invoice_total) AS uninvoiced_amount',
//				'(t1.invoice_total - t1.total_payment_received) AS total_payment_outstanding',
			));
			$command->from('(' . $baseCommand->getText() . ') AS t1');
			$command->params = $baseCommand->params;

			$pagination = new CPagination;
			$pagination->pageSize = isset($_GET['pagesize']) ? $_GET['pagesize'] : 20;

			$dataProvider = new CSqlDataProvider($command, array('totalItemCount' => $totalCount, 'pagination' => $pagination));

			$data['columns'] = $gridColumns;
			$data['dataProvider'] = $dataProvider;

			if ($action == 'Export') {
				Yii::import('common.lib.ECSVExport');
				$csv = new ECSVExport($command, FALSE);
				$csv->setHeaders(array(
					'submit_date' => 'Submit Date'
				));
				$csv->setCallback(function($row) {
					$new = array();
					foreach ($row as $k => $v) {
						switch ($k) {
							case 'approval_status':
								$v = PropertyCase::$approvalStatuses[$v];
								break;
//							case 'invoice_count':
//								$new['is_invoiced'] = $v == 0 ? 'N' : 'Y';
//								break;
						}

						$new[$k] = $v;
					}

					return $new;
				});

				$content = $csv->toCSV();
				Yii::app()->getRequest()->sendFile($data['type'] . '_' . $from_date . '_to_' . $to_date . '.csv', $content, "text/csv", true);

				Yii::app()->end();
			}
		}

		$this->render('export', $data);
	}

	public function actionCommissionPayables($from_date = null, $to_date = null, $action = null, $show_deleted = 0)
	{
		if ($action == 'Clear Filters') {
			$this->redirect(array($this->route));
		}

		$data = array();
		$data['from_date'] = isset($from_date) ? $from_date : date("Y") . '-01-01';
		$data['to_date'] = isset($to_date) ? $to_date : date('Y-m-d');
		$tmp_toDate = isset($to_date) ? date('Y-m-d', strtotime($to_date . ' + 1 day')) : date('Y-m-d');
		$data['show_deleted'] = $show_deleted;
		$data['type'] = 'Commission Payable';

		if (isset($from_date)) {
			$to_date = empty($to_date) ? date("Y-m-d") : $to_date;

			$gridColumns = array(
				array(
					'name' => 'date',
					'header' => 'Date',
					'htmlOptions' => array('width' => '40px'),
				),
				array(
					'name' => 'name',
					'header' => 'Name',
					'htmlOptions' => array('width' => '120px'),
				),
				array(
					'name' => 'license_no',
					'header' => 'License No',
					'htmlOptions' => array('width' => '120px'),
				),
				array(
					'name' => 'comm_type',
					'header' => 'Comm Type',
				),
				array(
					'name' => 'net_amount',
					'header' => 'Net Amount',
				),
				array(
					'name' => 'transaction_address',
					'header' => 'Transaction Address'
				),
				array(
					'name' => 'receipt_number',
					'header' => 'Receipt Number',
					'htmlOptions' => array('style' => 'text-align:left'),
				),
			);

			$sql = "SELECT DATE(t.create_time) as date,
					u.name, u.license_no,
					t.comm_type, t.net_amount, t.description,
					(SELECT  p.prop_address_formatted
						FROM `property_case` pc
						JOIN `property` p ON p.id=pc.property_id
						WHERE pc.id=t.property_case_id
					) as transaction_address,
					(SELECT pa.payment_number FROM `payment` pa WHERE pa.id=t.payment_id) as receipt_number
					FROM `payable_item` t
					JOIN `user` u ON u.party_id = t.payee_party_id
					WHERE t.`create_time` BETWEEN CONVERT_TZ( :from,  '+08:00',  '+00:00' ) AND CONVERT_TZ( :to,  '+08:00',  '+00:00' ) 
					AND t.`deleted` = 0 
					AND t.owner_party_id =:agencyPartyId 
					ORDER BY DATE(t.create_time) ASC";

			$command = Yii::app()->db->createCommand($sql);

			$selectedAgency = Yii::app()->user->getSelectedAgency();

			$command->params = array(
				':agencyPartyId' => $selectedAgency->party_id,
				':from' => $from_date . " 00:00:00",
				':to' => $tmp_toDate . " 00:00:00",
			);


			$countCommand = Yii::app()->db->createCommand();
			$countCommand->select(array('COUNT(*)'));
			$countCommand->from('(' . $command->getText() . ') AS t1');
			$countCommand->params = $command->params;
			$totalCount = $countCommand->queryScalar();

			$pagination = new CPagination;
			$pagination->pageSize = isset($_GET['pagesize']) ? $_GET['pagesize'] : 20;

			$dataProvider = new CSqlDataProvider($command, array('totalItemCount' => $totalCount, 'pagination' => $pagination));

			$data['columns'] = $gridColumns;
			$data['dataProvider'] = $dataProvider;

			if ($action == 'Export') {
				Yii::import('common.lib.ECSVExport');
				$csv = new ECSVExport($command, FALSE);
				$content = $csv->toCSV();
				Yii::app()->getRequest()->sendFile(str_replace(' ', '_', $data['type']) . '_' . $from_date . '_to_' . $to_date . '.csv', $content, "text/csv", true);

				Yii::app()->end();
			}
		}

		$this->render('export', $data);
	}

	public function actionTeamperformance($from_date = null, $to_date = null, $action = null, $leader_user_id = null)
	{
		if ($action == 'Clear Filters') {
			$this->redirect(array($this->route));
		}

		$data = array();
		$data['from_date'] = isset($from_date) ? $from_date : date("Y") . '-01-01';
		$data['to_date'] = isset($to_date) ? $to_date : date('Y-m-d');
		$tmp_toDate = isset($to_date) ? date('Y-m-d', strtotime($to_date . ' + 1 day')) : date('Y-m-d');
		$data['type'] = 'Team Performance';

		if (!is_null($leader_user_id)) {
			$leader = User::model()->ofCurrentAgency()->findByPk($leader_user_id);

			$data['leader_user_id'] = $leader_user_id;

			if (isset($from_date)) {
				$to_date = empty($to_date) ? date("Y-m-d") : $to_date;

				$gridColumns = array(
					array(
						'name' => 'submit_date',
						'header' => 'Submit Date',
						'htmlOptions' => array('width' => '40px'),
					),
					array(
						'name' => 'transaction_number',
						'header' => 'Transaction Number',
						'htmlOptions' => array('width' => '120px'),
					),
					array(
						'name' => 'transaction_address',
						'header' => 'Address',
					),
					array(
						'name' => 'price',
						'header' => 'Price',
					),
					array(
						'name' => 'commission_value',
						'header' => 'Commission Value',
					),
					array(
						'name' => 'role',
						'header' => 'Role'
					),
					array(
						'name' => 'is_external',
						'header' => 'external?',
					),
					array(
						'name' => 'name',
						'header' => 'name',
					),
					array(
						'name' => 'license_no',
						'header' => 'License No',
					),
					array(
						'name' => 'earn_comm_amount',
						'header' => 'Earn Comm Amount',
					),
				);

				$sql = "SELECT Date(pc.submit_time) as submit_date, pc.case_number as transaction_number, p.prop_address_formatted as transaction_address, pc.price, pc.comm_dollar_value as commission_value, 
					pca.role, pca.is_external, pca.name, pca.license_no, pca.earn_comm_amount, 
					(select 
					p.payment_date
					from `payment` p
					join `payment_application` pa ON pa.payment_id=p.id 
					join `invoice` i ON i.id = pa.invoice_id 
					join `invoice_item` ii on ii.invoice_id=i.id 
					Where p.payment_type_code='Receipt' and ii.property_case_id = pca.property_case_id and p.deleted=0 and ii.deleted=0 limit 1) as payment_date,
					(select 
					 (case when count(*)>0 then 'Paid'
					 else 'Unpaid'
					 end)
					from `invoice_item` ii
					join `payment_application` pa ON pa.invoice_id=ii.invoice_id 
					join `payment` p ON p.id = pa.payment_id 
					 Where ii.property_case_id = pca.property_case_id and ii.deleted=0 and p.deleted=0) as payment_status
					FROM `property_case_agent` pca
					JOIN `property_case` pc ON pc.id=pca.property_case_id
					JOIN `user` u ON u.id=pca.user_id
					JOIN `property` p ON p.id=pc.property_id 
					WHERE pca.deleted=0 AND pca.agency_id=:agencyId AND pc.status='Finalized' AND pc.approval_status=2 AND pc.deleted=0 
					AND pca.user_id in
					(SELECT id FROM `user` t
					WHERE t.agency_id=:agencyId AND t.upline_root_id=:leader_id AND t.lft > :leader_lft AND t.rgt < :leader_rgt) 
					AND pc.submit_time BETWEEN CONVERT_TZ( :from,  '+08:00',  '+00:00' ) AND CONVERT_TZ( :to,  '+08:00',  '+00:00' ) 
					ORDER By pc.submit_time";

				$command = Yii::app()->db->createCommand($sql);

				$selectedAgency = Yii::app()->user->getSelectedAgency();

				$command->params = array(
					':agencyId' => $selectedAgency->id,
					':leader_id' => $leader->id,
					':leader_lft' => $leader->lft,
					':leader_rgt' => $leader->rgt,
					':from' => $from_date . " 00:00:00",
					':to' => $tmp_toDate . " 00:00:00",
				);

				$countCommand = Yii::app()->db->createCommand();
				$countCommand->select(array('COUNT(*)'));
				$countCommand->from('(' . $command->getText() . ') AS t1');
				$countCommand->params = $command->params;
				$totalCount = $countCommand->queryScalar();

				$pagination = new CPagination;
				$pagination->pageSize = isset($_GET['pagesize']) ? $_GET['pagesize'] : 20;

				$dataProvider = new CSqlDataProvider($command, array('totalItemCount' => $totalCount, 'pagination' => $pagination));

				$data['columns'] = $gridColumns;
				$data['dataProvider'] = $dataProvider;
				$data['leader_user_id'] = $leader->id;

				if ($action == 'Export') {
					Yii::import('common.lib.ECSVExport');
					$csv = new ECSVExport($command, FALSE);
					$content = $csv->toCSV();
					Yii::app()->getRequest()->sendFile($leader->license_no . '_' . $from_date . '_to_' . $to_date . '.csv', $content, "text/csv", true);

					Yii::app()->end();
				}
			}
		}

		$this->render('teamperformance', $data);
	}

}

?>
